/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.util.Calendar;
import java.sql.*;
import java.util.Date;
import java.text.*;
import java.util.Vector;

/**
 *
 * @author Venus
 */
public class PhieuDatMA_Model {

    public static double Round(double Rval, int Rpl) {
        double p = (double) Math.pow(10, Rpl);
        Rval = Rval * p;
        double tmp = Math.round(Rval);
        return (double) tmp / p;
    }

    public Vector Detail(int maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String a = "";
            int z = 0;
            String query6 = "Select * from phieudatmonan where MaBan = " + maban + " and (KMHoaDon is null or KMHoaDon = 0) ";
            rss = stmt.executeQuery(query6);
            while (rss.next()) {
                z = z + 1;
            }
            if (z != 0) {
                String query = "select SoHDT, GioVao from phieudatmonan where MaBan = " + maban + " ";
                rss = stmt.executeQuery(query);
                int sohdt = 0;
                while (rss.next()) {
                    a = rss.getString(2);
                    sohdt = rss.getInt(1);
                }
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                String ngaygio[] = a.split(" ");
                String c = ngaygio[0];
                Calendar c1 = Calendar.getInstance();
                Calendar cbd = Calendar.getInstance();
                Calendar ckt = Calendar.getInstance();
                Date date1 = fm.parse(c);
                c1.setTime(date1);
                String query1 = " Select distinct(MaKM), NgayBD, NgayKT from khuyenmai where MaLKM = 2 and TinhTrang = 1 ";
                rss = stmt.executeQuery(query1);
                String ngaybatdau = "";
                String ngayketthuc = "";
                while (rss.next()) {
                    String y = rss.getString(2);
                    String x = rss.getString(3);
                    Date date2 = fm.parse(y);
                    Date date3 = fm.parse(x);
                    cbd.setTime(date2);
                    ckt.setTime(date3);
                    if (c1.before(ckt) && c1.after(cbd)) {
                        ngaybatdau = y;
                        ngayketthuc = x;
                    }
                    if (c1.equals(ckt) || c1.equals(cbd)) {
                        ngaybatdau = y;
                        ngayketthuc = x;
                    }
                }
                if (!ngaybatdau.equals("") && !ngayketthuc.equals("")) {
                    int GiamGia = 0;
                    double DieuKien = 0;
                    String query2 = " Select distinct(A.MaKM), GiamGia, DieuKien from khuyenmai A, kmtheohoadon B "
                            + " where A.MaKM = B.MaKM and A.MaLKM = 2 and NgayBD = '" + ngaybatdau + "' and NgayKT = '" + ngayketthuc + "' ";
                    rss = stmt.executeQuery(query2);
                    while (rss.next()) {
                        GiamGia = rss.getInt(2);
                        DieuKien = rss.getDouble(3);
                    }
                    double tamtinh = 0;
                    double thanhtien = 0;
                    int ppv = 0;
                    String query4 = "select ThanhTien, TongCong from phieudatmonan where SoHDT = " + sohdt + " ";
                    rss = stmt.executeQuery(query4);
                    while (rss.next()) {
                        thanhtien = rss.getDouble(1);
                        tamtinh = rss.getDouble(2);
                    }
                    if(thanhtien >= DieuKien){
                        double KMHD = (double) (thanhtien * GiamGia) / 100;
                        double TongCong = (double) tamtinh - KMHD;
                        String kyhieutt = TienTe_Model.LayKyHieuTT(1);
                        String query3 = "";
                        if(kyhieutt.equals("VND")){
                            query3 = " Update phieudatmonan set KMHoaDon = " + Round(KMHD, 0) + ", TongCong = " + Round(TongCong, 0) + " where SoHDT = " + sohdt + "  ";
                        }
                        else{
                            query3 = " Update phieudatmonan set KMHoaDon = " + Round(KMHD, 2) + ", TongCong = " + Round(TongCong, 2) + " where SoHDT = " + sohdt + "  ";
                        }
                        stmt.executeUpdate(query3);
                    }
                }
            }
            String query5 = "select SoHDT, MaBan, MaNVPV, GioVao, MaKH, ChietKhau, KMHoaDon, SoKhach, PhiPhucVu, ThanhTien, TongCong from phieudatmonan where MaBan = " + maban + " ";
            rss = stmt.executeQuery(query5);
            while (rss.next()) {
                Beans.PhieuDatMA b = new Beans.PhieuDatMA();
                b.setSoHDT(rss.getInt(1));
                b.setMaBan(rss.getInt(2));
                b.setMaNVPV(rss.getInt(3));
                b.setGioVao(rss.getString(4));
                b.setMaKH(rss.getInt(5));
                b.setChietKhau(rss.getInt(6));
                b.setKMHoaDon(rss.getDouble(7));
                b.setSoKhach(rss.getInt(8));
                b.setPhiPhucVu(rss.getInt(9));
                b.setThanhTien(rss.getDouble(10));
                b.setTongCong(rss.getInt(11));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
public Vector PDMA(int sohdt) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String a = "";
            int z = 0;
            String query6 = "Select * from phieudatmonan where SoHDT = " + sohdt + " and KMHoaDon is null ";
            rss = stmt.executeQuery(query6);
            while (rss.next()) {
                z = z + 1;
            }
            if (z != 0) {
                String query = "select SoHDT, GioVao from phieudatmonan where SoHDT = " + sohdt + " ";
                rss = stmt.executeQuery(query);
                while (rss.next()) {
                    a = rss.getString(2);                    
                }
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                String ngaygio[] = a.split(" ");
                String c = ngaygio[0];
                Calendar c1 = Calendar.getInstance();
                Calendar cbd = Calendar.getInstance();
                Calendar ckt = Calendar.getInstance();
                Date date1 = fm.parse(c);
                c1.setTime(date1);
                String query1 = " Select distinct(MaKM), NgayBD, NgayKT from khuyenmai where MaLKM = 2 and TinhTrang = 1 ";
                rss = stmt.executeQuery(query1);
                String ngaybatdau = "";
                String ngayketthuc = "";
                while (rss.next()) {
                    String y = rss.getString(2);
                    String x = rss.getString(3);
                    Date date2 = fm.parse(y);
                    Date date3 = fm.parse(x);
                    cbd.setTime(date2);
                    ckt.setTime(date3);
                    if (c1.before(ckt) && c1.after(cbd)) {
                        ngaybatdau = y;
                        ngayketthuc = x;
                    }
                    if (c1.equals(ckt) || c1.equals(cbd)) {
                        ngaybatdau = y;
                        ngayketthuc = x;
                    }
                }
                if (!ngaybatdau.equals("") && !ngayketthuc.equals("")) {
                    int GiamGia = 0;
                    String query2 = " Select distinct(A.MaKM), GiamGia from khuyenmai A, kmtheohoadon B "
                            + " where A.MaKM = B.MaKM and A.MaLKM = 2 and NgayBD = '" + ngaybatdau + "' and NgayKT = '" + ngayketthuc + "' ";
                    rss = stmt.executeQuery(query2);
                    while (rss.next()) {
                        GiamGia = rss.getInt(2);
                    }
                    double tamtinh = 0;
                    String query4 = "select TongCong from phieudatmonan where SoHDT = " + sohdt + " ";
                    rss = stmt.executeQuery(query4);
                    while (rss.next()) {
                        tamtinh = rss.getInt(1);
                    }
                    double KMHD = (double) (tamtinh * GiamGia) / 100;
                    double TongCong = (double) tamtinh - KMHD;
                    String query3 = " Update phieudatmonan set KMHoaDon = " + Round(KMHD, 2) + ", TongCong = " + Round(TongCong, 2) + " where SoHDT = " + sohdt + "  ";
                    stmt.executeUpdate(query3);
                }
            }
            String query5 = "select * from phieudatmonan where SoHDT = " + sohdt + " ";
            rss = stmt.executeQuery(query5);
            while (rss.next()) {
                Beans.PhieuDatMA b = new Beans.PhieuDatMA();
                b.setSoHDT(rss.getInt(1));
                b.setMaBan(rss.getInt(2));
                b.setMaNVPV(rss.getInt(3));
                b.setGioVao(rss.getString(4));
                b.setMaKH(rss.getInt(5));
                b.setChietKhau(rss.getInt(6));
                b.setKMHoaDon(rss.getDouble(7));
                b.setSoKhach(rss.getInt(8));
                b.setPhiPhucVu(rss.getInt(9));
                b.setThanhTien(rss.getDouble(10));
                b.setTongCong(rss.getDouble(11));
                v.add(b);

            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector DetailPDMA(int sohdt) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select  MaCTHDT, SoHDT, sum(SoLuong), MaMA, MaNVDB, MaDVT, GhiChu, MaTTMA, Gia, ThanhTien " +
                           " from chitietpdma where SoHDT = " + sohdt + " " +
                           " group by MaMA, MaDVT ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTPhieuDatMA b = new Beans.CTPhieuDatMA();
                b.setMaCTHDT(rss.getInt(1));
                b.setSoHDT(rss.getInt(2));
                b.setSoLuong(rss.getInt(3));
                b.setMaMA(rss.getInt(4));
                b.setMaNVDB(rss.getInt(5));
                b.setMaDVT(rss.getInt(6));
                b.setGhiChu(rss.getString(7));
                b.setMaTTMA(rss.getInt(8));
                b.setGia(rss.getDouble(9));
                b.setThanhTien(rss.getDouble(10));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static int LaySoHDT(int maban) {
        int mahdt = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select SoHDT from phieudatmonan where MaBan = " + maban + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                mahdt = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return mahdt;
    }

    public static int Notification() {
        int maht = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select count(*) from ban where MaTTBan = 8 or MaTTBan = 3 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public Vector LayDSBanTinhTien() {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKV) from ban where MaTTBan = 8 or MaTTBan = 3";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban ban = new Beans.Ban();
                ban.setMaKV(rss.getInt(1));
                v.add(ban);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSBan(int makv) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from ban where (MaTTBan = 8 or MaTTBan = 3) and MaKV = " + makv + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban ban = new Beans.Ban();
                ban.setMaBan(rss.getInt(1));
                ban.setMaKV(rss.getInt(2));
                ban.setMaTTBan(rss.getInt(3));
                ban.setTenBan(rss.getInt(4));
                ban.setSoGhe(rss.getInt(5));
                ban.setTinhTrang(rss.getByte(6));
                v.add(ban);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static String LayTenKV(int maban, int langid) {

        String tenkv = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenKV from khuvuc A, ban B where A.MaKV = B.MaKV and MaBan = " + maban + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }

    public static String LayTenBan(int maban) {

        String tenkv = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenBan from ban where  MaBan = " + maban + "  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }

    public static String LayKH(int makh) {

        String tenkv = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select HoTen from khachhang where  MaKH = " + makh + "  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }

    public static boolean UpdatePDMA(int makh, int chietkhau, int sohdt) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query1 = "select TongCong from phieudatmonan where SoHDT = " + sohdt + " ";
            rss = stmt.executeQuery(query1);
            double tamtinh = 0;
            while (rss.next()) {
                tamtinh = rss.getDouble(1);
            }
            double CK = (double) (tamtinh * chietkhau) / 100;
            double TongCong = (double) tamtinh - CK;
            String query = "Update phieudatmonan set MaKH = " + makh + ", ChietKhau = " + chietkhau + ", TongCong = " + Round(TongCong, 2) + " where SoHDT = " + sohdt + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int LayChietKhau(int makh) {
        int chietkhau = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(ChietKhau) from loaikhachhang A, khachhang B where A.MaLKH = B.MaLKH and B.MaKH = " + makh + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                chietkhau = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return chietkhau;
    }

    public static boolean InHoaDon(String sohdt, String tienkhach, int MaNVTN) {
        Connection conn = null;
        ResultSet rss = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select * from phieudatmonan where SoHDT = " + sohdt + "  ";
            rss = stmt.executeQuery(query);
            int MaBan = 0;
            int MaNVPV = 0;
            String GioVao = "";
            int MaKH = 0;
            int SoKhach = 0;
            int ChietKhau = 0;
            int PhiPhucVu = 0;
            double KMHoaDon = 0;
            double ThanhTien = 0;
            double TongCong = 0;
            while (rss.next()) {
                MaBan = rss.getInt(2);
                MaNVPV = rss.getInt(3);
                GioVao = rss.getString(4);
                MaKH = rss.getInt(5);
                ChietKhau = rss.getInt(6);
                KMHoaDon = rss.getInt(7);
                SoKhach = rss.getInt(8);
                PhiPhucVu = rss.getInt(9);
                ThanhTien = rss.getDouble(10);
                TongCong = rss.getDouble(11);
            }
            double tienthoilai = Double.parseDouble(tienkhach) - TongCong;
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
            String ngaygio = fm.format(cal.getTime());
            String kyhieu = TienTe_Model.LayKyHieuTT(1);
            String query1 = "";
            if(kyhieu.equals("VND")){
                query1 = "Insert into hoadon values ( " + sohdt + ", " + MaBan + ", " + MaNVPV + ", " + MaNVTN + ", '" + GioVao + "', '" + ngaygio + "', " + MaKH + ", " + SoKhach + ", " + ChietKhau + "," + PhiPhucVu + ", " + KMHoaDon + ", " + ThanhTien + ", " + Round(TongCong,0) + ", " + tienkhach + ", " + Round(tienthoilai,0) + ") ";
            }
            else{
                query1 = "Insert into hoadon values ( " + sohdt + ", " + MaBan + ", " + MaNVPV + ", " + MaNVTN + ", '" + GioVao + "', '" + ngaygio + "', " + MaKH + ", " + SoKhach + ", " + ChietKhau + "," + PhiPhucVu + ", " + KMHoaDon + ", " + ThanhTien + ", " + Round(TongCong, 2) + ", " + tienkhach + ", " + Round(tienthoilai, 2) + ") ";
            }
            stmt.executeUpdate(query1);
            if(MaKH != 0){                
                String query2 = " select SoTien, SoDiem from doidiem ";
                rss = stmt.executeQuery(query2);
                int sotien = 0;
                int sodiem = 0;
                while (rss.next()) {
                    sotien = rss.getInt(1);
                    sodiem = rss.getInt(2);
                }
                int x = (int)TongCong;
                if(x >= sotien){
                    int mark = ( x / sotien) * sodiem;
                    int Diem = 0;
                    String query3 = " Select DiemTL from khachhang where MaKH = "+MaKH+"  ";
                    rss = stmt.executeQuery(query3);
                    while (rss.next()) {
                        Diem = rss.getInt(1);
                    }
                    int DiemTL = Diem + mark;
                    String query4 = " update khachhang set DiemTL = "+DiemTL+" where MaKH = "+MaKH+" ";
                    stmt.executeUpdate(query4);
                    String query5 = " Select SoDiem from loaikhachhang where MaLKH = 2 ";
                    int DiemLKH2 = 0;
                    rss = stmt.executeQuery(query5);
                    while (rss.next()) {
                        DiemLKH2 = rss.getInt(1);
                    }
                    String query6 = " Select SoDiem from loaikhachhang where MaLKH = 1 ";
                    int DiemLKH1 = 0;
                    rss = stmt.executeQuery(query6);
                    while (rss.next()) {
                        DiemLKH1 = rss.getInt(1);
                    }
                    String query7 = "";
                    if( DiemTL < DiemLKH2 ){
                        query7 = " Update khachhang set MaLKH = 3 where MaKH = "+MaKH+" ";
                    }
                    else if( DiemLKH2 <= DiemTL && DiemTL < DiemLKH1){
                        query7 = " Update khachhang set MaLKH = 2 where MaKH = "+MaKH+" ";
                    }
                    else{
                        query7 = " Update khachhang set MaLKH = 1 where MaKH = "+MaKH+" ";
                    }
                    stmt.executeUpdate(query7);
                }
            }
            stmt.close();
            rss.close();
            
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return true;
    }

    public static boolean ChiTietHoaDon(String sohdt) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaCTHDT, SoHDT, sum(SoLuong), MaMA, MaNVDB, MaDVT, GhiChu, MaTTMA, Gia, ThanhTien, MaNVDauBep " +
                           " from chitietpdma where SoHDT = " + sohdt + " " +
                           " Group by MaMA, MaDVT  ";
            rss = stmt.executeQuery(query);
            int MaMA = 0;
            int MaDVT = 0;
            int SoLuong = 0;
            
            double Gia = 0;
            double ThanhTien = 0;
            int MaNVDauBep = 0;
            String x = " Insert into chitiethd values ";
            while (rss.next()) {
                MaMA = rss.getInt(4);
                MaDVT = rss.getInt(6);
                SoLuong = rss.getInt(3);
                Gia = rss.getInt(9);                
                ThanhTien = rss.getInt(10);
                MaNVDauBep = rss.getInt(11);
                x = x + "( " + sohdt + ", " + MaMA + ", " + MaDVT + ", " + MaNVDauBep + ", " + SoLuong + ", " + Gia + ", " + ThanhTien + ") ,";
            }
            String query1 = x.substring(0, x.length() - 1);
            stmt.executeUpdate(query1);
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return true;
    }

    public static double LayTienThoiLai(String sohdt, String tienkhach) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        double tamtinh = 0;
        double tienthoilai = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TamTinh from phieudatmonan where SoHDT = " + sohdt + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tamtinh = rss.getDouble(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        tienthoilai = Double.parseDouble(tienkhach) - tamtinh;

        return tienthoilai;
    }

    public static boolean XoaPDMA(String sohdt) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Delete from phieudatmonan where SoHDT = " + sohdt + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean XoaCTPDMA(String sohdt) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Delete from chitietpdma where SoHDT = " + sohdt + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CapNhatHoaDon(String ban)
    {
         Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String query = "";
        try {
            int sohdt = 0;
            query = "select max(sohdt) from phieudatmonan where maban = "+ban;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                sohdt = rss.getInt(1);
            }
            if(sohdt == 0)
            {
                rss.close();
                stmt.close();
                conn.close();
                return false;
            }

            query = "select sum(thanhtien) from chitietpdma where sohdt = " + sohdt;
            rss = stmt.executeQuery(query);
            double thanhtien = 0d;
            while (rss.next()) {
                thanhtien = rss.getDouble(1);
            }
            query = "update phieudatmonan set thanhtien = " + thanhtien + ",tongcong=thanhtien+(phiphucvu*thanhtien/100)-(chietkhau*thanhtien/100) where sohdt=" + sohdt;
            stmt.executeUpdate(query);
            query = "update ban set mattban = 2 where maban=" + ban;
            stmt.executeUpdate(query);
            rss.close();
            stmt.close();
            conn.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean LapPhieu(String ban, String nv, String sokhach, int langid, Vector v) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            Calendar now = Calendar.getInstance();
            int h = now.get(Calendar.HOUR_OF_DAY);
            int p = now.get(Calendar.MINUTE);
            String kv = String.valueOf(Ban_Model.LayMaKV(ban)) ;
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
            String ngaygio = fm.format(now.getTime());
            String matd = ThoiDiem_Model.TimMaTD(h, p, langid);
            //int phiphucvu = PhiPV_Model.LoadPPV(kv, matd);
            int phiphucvu =0;
            phiphucvu = PhiPV_Model.LoadPPV(kv, matd);
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int sohdt = 0;
            String query = "select sohdt from phieudatmonan where maban = "+ban;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                sohdt = rss.getInt(1);
            }
             int n = 0;
            if(sohdt == 0)
            {
                 query = " insert into phieudatmonan(maban,manvpv,giovao,chietkhau,sokhach,phiphucvu,thanhtien,tongcong) values "
                    + " ("+ ban +", " + nv + ",'" + ngaygio + "',0," + sokhach + "," + phiphucvu + ",0,0)";
                n = stmt.executeUpdate(query);
                 if (n == 0) {
                    stmt.close();
                    conn.close();
                    return false;// thất bại
                }
                query = "select max(sohdt) from phieudatmonan where maban = "+ban;
                rss = stmt.executeQuery(query);
                while (rss.next()) {
                    sohdt = rss.getInt(1);
                }
            }         
            int kq = 0;
            int size = v.size();
            for (int i = 0; i < size; i++) {
                String[] item = (String[]) v.get(i);
                    int gia = MonAn_Model.LoadGia(item[0], item[2]);
                query = " insert into chitietpdma(SoHDT,SoLuong,MaMA,MaNVDB,MaDVT,GhiChu,MaTTMA,Gia,ThanhTien,HoiThuc) values "
                        + " (" + sohdt + ", " + item[7] + "," + item[0] + "," + nv + "," + item[2] + ",'" + item[6] + "',1," + gia + "," + gia * Integer.parseInt(item[7]) + ",0)";
                kq = stmt.executeUpdate(query);
                if (kq == 0) {
                    rss.close();
                    stmt.close();
                    conn.close();
                    return false;// thất bại
                }           
            }
            query = "select sum(thanhtien) from chitietpdma where sohdt = " + sohdt;
            rss = stmt.executeQuery(query);
            double thanhtien = 0d;
            while (rss.next()) {
                thanhtien = rss.getDouble(1);
            }
            query = "update phieudatmonan set thanhtien = " + thanhtien + ",tongcong=thanhtien+(phiphucvu*thanhtien/100)-(chietkhau*thanhtien/100) where sohdt=" + sohdt;
            kq = stmt.executeUpdate(query);
            query = "update ban set mattban = 2 where maban=" + ban;
            kq = stmt.executeUpdate(query);
            rss.close();
            stmt.close();
            conn.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static Vector LoadCart(String ban,int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select CT.MaCTHDT,TenMA,SoLuong,TenDVT,CT.MaTTMA,Gia,TenTTMA from chitietpdma CT,monan MA, donvitinh DVT, phieudatmonan PD, tinhtrangmonan TT where CT.MaTTMA = TT.MaTTMA and CT.MaMA = MA.MaMA and  CT.MaDVT = DVT.MaDVT and CT.SoHDT = PD.SoHDT and maban = "+ ban+" and MA.MaNN = "+langid+ " and DVT.MaNN ="+langid + " and TT.MaNN ="+langid;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                String[] item = new String[7];
                item[0] = String.valueOf(rss.getInt(1));
                item[1] = rss.getString(2);
                item[2] = String.valueOf(rss.getInt(3));
                item[3] = rss.getString(4);
                item[4] = String.valueOf(rss.getInt(5));
                item[5] = String.valueOf(rss.getInt(6));
                item[6] = rss.getString(7);
                v.add(item);
            }
            stmt.close();
            rss.close();
            conn.close();
            return v;
        } catch (Exception e) {
            return null;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }       
    }
    public static boolean HoiThuc(String MaCTHDT) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "update chitietpdma set hoithuc =1 where MaCTHDT = "+MaCTHDT;
            stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            return true;
        } catch (Exception e) {
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean GoiThem(String MaCTHDT) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "update chitietpdma set hoithuc =1 where MaCTHDT = "+MaCTHDT;
            stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            return true;
        } catch (Exception e) {
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean XoaMA(String MaCTHDT) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "delete from chitietpdma  where MaTTMA = 1 and MaCTHDT = "+MaCTHDT;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if(n >0)
            return true;
            else
                return false;
        } catch (Exception e) {
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
